package sliver.dao.impl;

import java.io.Serializable;
import java.math.BigInteger;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.hibernate.jdbc.Work;
import org.springframework.stereotype.Repository;

import sliver.dao.CategoryDao;
import sliver.entity.Category;
import sliver.entity.Shopper;

@Repository
public class CategoryDaoImpl extends BaseDaoImpl<Category> implements CategoryDao{
	
	@Override
	public List<Category> findParentCategories(Serializable categoryId) {
		
		String queryString = "SELECT category1 " +
							 "FROM Category category1, Category category2 " +
							 "WHERE category1.id = category2.parent.id "  +
							 "AND category2.id = ? ";
		
		List<Category> categories = complexQuery(queryString, new Object[]{categoryId});
		
		return categories;
	}
	
	@Override
	public List<Category> findCategorise(Shopper shopper, boolean isRoot, Serializable parentId) {
		
		String queryString = "SELECT id, name, parent_id, deep " +
							 "FROM (" +
							 	"SELECT * " +
							 	"FROM box_category category " +
							 	"WHERE category.shopper_id = " + shopper.getId();
		
		Shopper parent = shopper.getParent();
		while(parent != null){
			queryString += " AND category.shopper_id = " + parent.getId();
			
			parent = parent.getParent();
		}
		
		queryString += ") category WHERE category.parent_id ";
		
		if(isRoot)
			queryString += "is null";
		else
			queryString += "= " + parentId;
		
		List<Object> rows = complexQueryFields(queryString, new Object[]{});
		
		List<Category> categories = new ArrayList<Category>();
		for(Object row : rows){
			Object[] datas = (Object[])row;
			Category category = new Category();
			
			BigInteger id = (BigInteger)datas[0];
			category.setId(id.longValue());
			category.setName((String)datas[1]);
			category.setParent(findById(((BigInteger)datas[0]).longValue(), Category.class));
			category.setDeep((Integer)datas[3]);
			category.setShopper(shopper);
			
			categories.add(category);
		}
			
		return categories;
	}
	
	@Override
	public List<Category> findMyRootCategories(Shopper shopper) {
		
		StringBuilder shopperWhereBuilder = new StringBuilder();
		shopperWhereBuilder.append("(")
						   .append(shopper.getId());
		Shopper parent = shopper.getParent();
		if(parent != null){
			
			shopperWhereBuilder.append(", ");
			while(parent != null){
				shopperWhereBuilder.append(parent.getId()).append(", ");
			}
			
			shopperWhereBuilder = shopperWhereBuilder.deleteCharAt(shopperWhereBuilder.length()-1);
		}
		shopperWhereBuilder.append(")");
		
		
		String queryString = "SELECT category " +
							 "FROM Category category " +
							 "WHERE category.parent.id is null " +
							 "AND category.shopper.id in " + shopperWhereBuilder.toString();
		
		System.out.println(queryString);
		
		return complexQuery(queryString, new Object[]{});
	}
	
	@Override
	public List<Category> findMyCategories(final Shopper shopper,final Long rootId) {
		
		final List<Category> categories = new ArrayList<Category>();
		
		getCurrentSession().doWork(new Work() {
			
			@Override
			public void execute(Connection connection) throws SQLException {
				CallableStatement callableStatement = null;
				
				callableStatement = connection.prepareCall("{call findCategories(?,10,?,10)}");
				callableStatement.setInt(1, shopper.getId());
				callableStatement.setLong(2, rootId);
				
				boolean isHandle = callableStatement.execute();
				while(isHandle){
					
					ResultSet sets = callableStatement.getResultSet();
					while(sets != null && sets.next()){
						
						Long id = sets.getLong(1);
						String name = sets.getString(2);
						int deep = sets.getInt(3);
						Category parent = findById(sets.getLong(4), Category.class);
						Shopper shopper = new Shopper();
						shopper.setId(sets.getInt(5));
						
						Category category = new Category();
						category.setId(id);
						category.setName(name);
						category.setDeep(deep);
						category.setParent(parent);
						category.setShopper(shopper);
						
						categories.add(category);
					}
					
					isHandle = callableStatement.getMoreResults();
				}
			}
		});
		
		return categories;
	}
	
}
